﻿using GalaSoft.MvvmLight.Messaging;
using MySql.Data.MySqlClient;
using SPI_TOOLS.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace SPI_TOOLS.Services
{
    public class DatabaseHelper
    {
        private string connectionString = "server=localhost;port=3306;database=mydb;user=root;password=root;";

        public DatabaseHelper() { }

        // 查询所有用户
        public List<PanelDataModel> GetAllPanel(DateTime starttime, DateTime endtime, string machineName, ref ProgressMsg progressmsg)
        {
            progressmsg.JobName = "查询Panel信息";
            progressmsg.CurrentJobCount = 0;
            progressmsg.AllJobCount = -1;
            progressmsg.Cmd = ProgressMsgType.go;
            Messenger.Default.Send<ProgressMsg>(progressmsg, ProgressMsg.Progress_Msg_Key);

            var panels = new List<PanelDataModel>();

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string query = "SELECT id, barcode, state, component_total, alarm_total, Rs_ok_total,";
                    query += "Rs_Ng_total, length, width, create_time, Confirm_Time, recipe_code FROM panel where create_time > ";
                    query += $" \'{starttime.ToString()}\' and create_time < \'{endtime.ToString()}\'";
                    if (machineName != null && machineName != "") 
                    {
                        query += $" and recipe_code=\'{machineName}\'";
                    }

                    using (var command = new MySqlCommand(query, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                progressmsg.CurrentJobCount++;
                                Messenger.Default.Send<ProgressMsg>(progressmsg, ProgressMsg.Progress_Msg_Key);

                                PanelDataModel  panel = new PanelDataModel();
                                
                                panel.Id = reader.GetString("Id");
                                //var barcode
                                //if (reader.GetString("barcode") != null)
                                //{
                                //    panel.Barcode = reader.GetString("barcode");
                                //}
                                //else
                                //{
                                //    panel.Barcode = "";
                                //}
                                panel.State = reader.GetInt32("state");
                                panel.ComponentTotal = reader.GetInt32("component_total");
                                panel.AlarmTotal = reader.GetInt32("alarm_total");
                                panel.R_ok_Total = reader.GetInt32("Rs_ok_total");
                                panel.R_ng_Total = reader.GetInt32("Rs_Ng_total");
                                panel.CreateTime = reader.GetDateTime("create_time");
                                //panel.ConfirmTime = reader.GetDateTime("Confirm_Time");
                                panel.RecipeCode = reader.GetString("recipe_code");
                                panel.Width = reader.GetDouble("width");
                                panel.Height = reader.GetDouble("length");
                                panels.Add(panel);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"数据库查询错误: {ex.Message}");
                }
            }

            return panels;
        }
        public List<BoardDataModel> GetBoards(List<PanelDataModel> panels, ref ProgressMsg msg)
        {
            msg.CurrentJobCount = 0;
            msg.JobName = "获取Board";
            msg.AllJobCount = panels.Count;
            msg.Cmd = ProgressMsgType.go;
            Messenger.Default.Send<ProgressMsg>(msg, ProgressMsg.Progress_Msg_Key);

            var boards = new List<BoardDataModel>();

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    foreach (var panel in panels)
                    {
                        string query =
                            $"SELECT id, Board_Number, state, component_total, barcode FROM board where Panel_id ={panel.Id} ";
    
                        using (var command = new MySqlCommand(query, connection))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                boards.Add(new BoardDataModel
                                {
                                    Id = reader.GetString("Id"),
                                    State = reader.GetInt32("state"),
                                    ComponentTotal = reader.GetInt32("component_total"),
                                    Number = reader.GetInt32("board_number"),
                                    RecipeCode = panel.RecipeCode,
                                });
                            }
                        }
                        msg.CurrentJobCount++;
                        msg.Cmd = ProgressMsgType.go;
                        Messenger.Default.Send<ProgressMsg>(msg, ProgressMsg.Progress_Msg_Key);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"数据库查询错误: {ex.Message}");
                }
            }

            return boards;
        }
        public List<ComponentDataModel> GetComponent(List<PanelDataModel> panels)
        {
            var components = new List<ComponentDataModel>();

            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    foreach (var panel in panels)
                    {
                        string query =
                            $"SELECT id, state FROM component where Panel_id ={panel.Id} ";

                        using (var command = new MySqlCommand(query, connection))
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                components.Add(new ComponentDataModel
                                {
                                    Id = reader.GetString("Id"),
                                    State = reader.GetInt32("state"),
                                    Panel_id = panel.Id
                                });
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"数据库查询错误: {ex.Message}");
                }
            }

            return components;
        }
    }
}
